Imports System.IO
Imports System.Web
Imports System.Text

Public Class pvWorkBook

    'Property for holding dataset
    Private workbook_ds As DataSet
    Property datasource() As DataSet
        Get
            datasource = workbook_ds
        End Get
        Set(ByVal Value As DataSet)
            workbook_ds = Value
        End Set
    End Property

    'Properties for error handling
    Private strErrorSource As String
    ReadOnly Property errorsource() As String
        Get
            errorsource = strErrorSource
        End Get
    End Property

    Private bolError As Boolean
    ReadOnly Property err() As Boolean
        Get
            err = bolError
        End Get
    End Property

    'Set the workpath
    Private strPath As String
    Property workpath() As String
        Get
            workpath = strPath
        End Get
        Set(ByVal Value As String)
            strPath = Value
        End Set
    End Property


    Sub New()
        'Entry point
    End Sub


    Public Sub renderWorkbook(ByVal ds As DataSet)
        'Send Workbook to browser
        createworksheets(workbook_ds)

    End Sub

    Private Sub createworksheets(ByVal ds As DataSet)
        'Create a collection of names based on tables in the dataset
        'Create a file for each table
        Dim tbl As DataTable
        Dim colSheets As New Collection()
        Try
            'create directory that holds worksheets and support files
            'deletes directory and files if the exist
            createDirectory(strPath)
            createDirectory(strPath & "\" & ds.DataSetName & "_files")
            'Walk through each table in dataset and create worksheet
            For Each tbl In ds.Tables
                colSheets.Add(tbl.TableName.ToString)
                'Pass datatable to be exported to file
                createWorksheetFile(tbl, ds.DataSetName.ToString)
            Next
            'Support files 
            createSupportFiles(colSheets, ds.DataSetName.ToString)

            'The main workbook.xls
            createXls(colSheets, ds.DataSetName.ToString)

        Catch e As Exception
            pv_error(e, "createworksheets")
        End Try

    End Sub

    Private Sub createDirectory(ByVal strName As String)
        'Create directory based on workbookname
        'Directory to create
        'bookname_files/
        Try
            'If Directory.Exists(strPath) Then cleanupfiles(strPath)
            Directory.CreateDirectory(strName)
        Catch e As Exception
            pv_error(e, "createDirectory")
        End Try
    End Sub

    Private Sub createXls(ByVal colSheets As Collection, ByVal strFileName As String)
        Dim strSheet As String
        'create the main exls file
        Dim myHtml As New StringBuilder()
        'Using a function to build header
        myHtml.Append(createMainHeader)
        'Title of the book
        'TODO: Make this variable
        myHtml.Append("<title>" & strFileName & "</title>" & vbCrLf)
        myHtml.Append("<xml>" & vbCrLf)
        myHtml.Append("<x:ExcelWorkbook>" & vbCrLf)
        myHtml.Append("<x:ExcelWorksheets>" & vbCrLf)

        'Create references for each worksheet
        For Each strSheet In colSheets
            myHtml.Append("<x:ExcelWorksheet>" & vbCrLf)
            myHtml.Append("<x:Name>" & strSheet.ToString & "</x:Name>" & vbCrLf)
            myHtml.Append("<x:WorksheetSource HRef='./" & strFileName & "_files/" & strSheet & ".htm'/>" & vbCrLf)
            myHtml.Append("</x:ExcelWorksheet>" & vbCrLf)
        Next
        'Use function to write end of file
        myHtml.Append(createMainFooter)
        writeToFile(myHtml.ToString, strPath & "\" & strFileName & ".xls" & vbCrLf)

    End Sub

    Private Sub createSupportFiles(ByVal colSheets As Collection, ByVal strWorkbookName As String)
        'bookname_files/fileist.xml
        Dim strHtml As New StringBuilder()
        Dim strSheet As String
        strHtml.Append("<xml xmlns:o='urn:schemas-microsoft-com:office:office'>" & vbCrLf)
        strHtml.Append("<o:MainFile HRef='../" & strWorkbookName & ".xls'/>" & vbCrLf)
        For Each strSheet In colSheets
            strHtml.Append("<o:File HRef='" & strSheet & ".htm'/>" & vbCrLf)
        Next
        strHtml.Append("<o:File HRef='filelist.xml'/>" & vbCrLf)
        strHtml.Append("</xml>")
        writeToFile(strHtml.ToString, strPath & "\" & strWorkbookName & "_files\filelist.xml" & vbCrLf)
        'Files to create
        'bookname.xls

        'bookname_files/stylesheet.css
        'bookname_files/tabstrip.htm
    End Sub

    Private Sub createWorksheetFile(ByVal tbl As DataTable, ByVal strName As String)
        Dim myFileName, myFileNamePath As String
        Dim strHTML As New StringBuilder()
        Dim rw As DataRow

        Dim intCount As Integer = tbl.Columns.Count
        Dim I As Integer
        'Create file based on data table passed use optional sheetname if provided
        myFileName = tbl.TableName.ToString

        myFileNamePath = strPath & "\" & strName & "_files\" & myFileName & ".htm"

        'Create header
        strHTML.Append(createSheetHeader(strName & ".xls"))
        strHTML.Append("<body>" & vbCrLf)
        strHTML.Append("<table x:str border=1 cellpadding=0 cellspacing=0 width=192 style='border-collapse:" & vbCrLf)
        strHTML.Append(" collapse;table-layout:fixed;width:144pt'" & vbCrLf)

        'Create sheet headings
        strHTML.Append("<tr>" & vbCrLf)
        For I = 0 To intCount - 1
            strHTML.Append("<td><b>" & tbl.Columns(I).ColumnName.ToString & "</b></td>" & vbCrLf)
        Next
        strHTML.Append("</tr>" & vbCrLf)
        'Turn each row into a <tr>
        Dim strItem As String
        For Each rw In tbl.Rows
            strHTML.Append("<tr>" & vbCrLf)
            'Convert each item into <td>
            For I = 0 To intCount - 1
                strItem = rw.Table.Columns(I).ColumnName.ToString
                strHTML.Append("<td>" & fixNull((rw.Item(I))) & "</td>" & vbCrLf)
            Next
            strHTML.Append("</tr>" & vbCrLf)
        Next
        strHTML.Append("</table>" & vbCrLf)
        strHTML.Append("</html>" & vbCrLf)

        writeToFile(strHTML.ToString, myFileNamePath)

        'Files to create
        'bookname_files/sheet001.htm
        'bookname_files/sheet002.htm 
        'bookname_files/sheet(etc.).htm 
    End Sub

    Private Function createSheetHeader(ByVal strFileName As String) As String
        Dim strHTML As New StringBuilder()
        strHTML.Append("<html xmlns:o=" & """urn:schemas-microsoft-com:office:office""" & vbCrLf)
        strHTML.Append("xmlns:x=" & """ urn:schemas-microsoft-com:office:excel& """ & vbCrLf)
        strHTML.Append("xmlns=" & """ http://www.w3.org/TR/REC-html40>& """ & vbCrLf)
        strHTML.Append("<head>" & vbCrLf)
        strHTML.Append("<meta http-equiv=Content-Type content=" & """text/html; charset=windows-1252" & """ >" & vbCrLf)
        strHTML.Append("<meta name=ProgId content=Excel.Sheet>" & vbCrLf)
        strHTML.Append("<meta name=Generator content=" & """Microsoft Excel 9" & """>" & vbCrLf)
        strHTML.Append("<link id=Main-File rel=Main-File href=" & """../" & strFileName & ".xls" & """>" & vbCrLf)
        strHTML.Append("<xml>" & vbCrLf)
        strHTML.Append("<x:WorksheetOptions>" & vbCrLf)
        strHTML.Append("<x:ProtectContents>False</x:ProtectContents>" & vbCrLf)
        strHTML.Append("<x:ProtectObjects>False</x:ProtectObjects>" & vbCrLf)
        strHTML.Append("<x:ProtectScenarios>False</x:ProtectScenarios>" & vbCrLf)
        strHTML.Append("</x:WorksheetOptions>" & vbCrLf)
        strHTML.Append("</xml>" & vbCrLf)
        strHTML.Append("</head>" & vbCrLf)
        createSheetHeader = strHTML.ToString
    End Function

    'Creates header form main xls document
    Private Function createMainHeader() As String
        Dim myString As New StringBuilder()
        myString.Append("<html xmlns:o=" & """ urn:schemas-microsoft-com:office:office""" & vbCrLf)
        myString.Append("xmlns:x=" & """urn:schemas-microsoft-com:office:excel""" & vbCrLf)
        myString.Append("xmlns=" & """http://www.w3.org/TR/REC-html40>""" & vbCrLf)
        myString.Append("<head>" & vbCrLf)
        myString.Append("<meta name=" & """Excel Workbook Frameset""" & ">" & vbCrLf)
        myString.Append("<meta http-equiv=Content-Type content=" & """text/html; charset=windows-1252""" & ">" & vbCrLf)
        myString.Append("<meta http-equiv=Content-Disposition: " & """attachment;""" & " >")
        myString.Append("<meta name=ProgId content=Excel.Sheet>" & vbCrLf)
        myString.Append("<meta name=Generator content=" & """Microsoft Excel 9""" & ">" & vbCrLf)
        createMainHeader = myString.ToString
    End Function

    'Create footer of main.xls
    Private Function createMainFooter() As String
        Dim myHtml As New StringBuilder()
        myHtml.Append("</x:ExcelWorksheets>" & vbCrLf)
        myHtml.Append("<x:WindowHeight>6795</x:WindowHeight>" & vbCrLf)
        myHtml.Append("<x:WindowWidth>11340</x:WindowWidth>" & vbCrLf)
        myHtml.Append("<x:WindowTopX>360</x:WindowTopX>" & vbCrLf)
        myHtml.Append("<x:WindowTopY>75</x:WindowTopY>" & vbCrLf)
        myHtml.Append("<x:ActiveSheet>0</x:ActiveSheet>" & vbCrLf)
        myHtml.Append("<x:ProtectStructure>False</x:ProtectStructure>" & vbCrLf)
        myHtml.Append("<x:ProtectWindows>False</x:ProtectWindows>" & vbCrLf)
        myHtml.Append("</x:ExcelWorkbook>" & vbCrLf)
        myHtml.Append("</xml>" & vbCrLf)
        myHtml.Append("</head>" & vbCrLf)
        myHtml.Append("</html>" & vbCrLf)
        createMainFooter = myHtml.ToString
    End Function

    Private Sub cleanupfiles(ByVal strPath As String)
        'Deletes all files from a directory and the directory
        Dim strFile As String
        Try
            For Each strFile In Directory.GetFiles(strPath)
                File.Delete(strFile)
            Next
            Directory.Delete(strPath)
        Catch e As Exception
            pv_error(e, "cleanupfiles")
        End Try
    End Sub

    Private Sub deletefile(ByVal strFilePath As String)
        'Delete a single file
        File.Delete(strFilePath)
    End Sub

    Private Sub writeToFile(ByVal strFile As String, ByVal strPathFileName As String)
        'Write to file
        Dim writer As StreamWriter
        writer = New StreamWriter(strPathFileName, False)
        writer.WriteLine(strFile)
        writer.Close()
    End Sub

    'Error code
    'TODO: return error message and write to log
    Private Sub pv_error(ByVal e As Exception, ByVal strSource As String)
        bolError = True
        strErrorSource = e.Message & "Source: " & strSource
    End Sub

    Private Function fixNull(ByVal fld As Object) As String
        Dim myString As String
        Dim m As RegularExpressions.Match()
        Dim r As RegularExpressions.Regex
        r = New RegularExpressions.Regex("\d{9,100}")

        Dim myType As String = fld.GetType.ToString
        If fld Is DBNull.Value Then
            fixNull = ""
        Else
            myString = fld
            'See if it's 11 or longer 
            'See if it's a numeric field
            If r.IsMatch(myString) Then
                fixNull = Trim(myString).ToString
                'We put a space infront to preserve formating
                'fixNull = """ &nbsp;" & Trim(myString) & """"
                fixNull = "&nbsp;" & Trim(myString)
            Else
                fixNull = Trim(myString).ToString
            End If
        End If
    End Function
End Class

